﻿// --------------------------------
// <copyright file="TSPMembership.cs" company="Samaj Shekhar">
//     Copyright (c) 2010-2011 Samaj Shekhar
// </copyright>
// <author>Samaj Shekhar (samaj.shekhar@hotmail.com)</author>
// <license>Released under the terms of the Microsoft Public License (Ms-PL)</license>
// <website>http://thesharepage.codeplex.com</website>
// ---------------------------------

using System;
using System.Configuration;
using System.Collections.Specialized;
using System.Configuration.Provider;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;
using System.Web.Security;
using SharePage.CoreLib.Utils.ExtentionMethods;

namespace SharePage.CoreLib
{

    /// <summary>
    /// Exposes and updates membership user information along with custom data, like facebook and other services in the membership data store.
    /// </summary>
    /// <remarks>
    /// Copyright: Copyright (c) 2010-2011 Samaj Shekhar
    /// Last Update: 13-Jan-2011|4:46am
    /// </remarks>
    public class TSPMembershipUser : MembershipUser
    {
        /// <summary>
        /// Exposes Information related to a facebook user
        /// </summary>
        public FacebookMemberUserData FacebookUser { get;  set; }
    }

    /// <summary>
    /// A class to expose a Facebook user's information needed in a running session
    /// </summary>
    /// <remarks>
    /// Copyright: Copyright (c) 2010-2011 Samaj Shekhar
    /// Last Update: 13-Jan-2011|9:51pm
    /// </remarks>
    public class FacebookMemberUserData
    {
        public string UserName { get; set; }
        public long FBUid { get; set; }
        public string EmailID { get; set; }
        public string OAuthAccessToken { get; set; }
        public DateTime ExpiresOn { get; set; }
        public string ExtendedPermissions { get; set; }
        public DateTime LastUpdated { get; set; }
    }

    /// <summary>
    /// Contains the strings for the setting names in the Web.Config file. 
    /// </summary>
    /// <remarks>
    /// Update this Class to the string names in the Web.Config file.
    /// Copyright: Copyright (c) 2010-2011 Samaj Shekhar
    /// Last Update: 13-Jan-2011|9:53pm
    /// </remarks>
    public static class TSPmembershipSettingStrings
    {
        public static string NewPasswordLength { get { return "newPasswordLength"; } }
        public static string ConnectionString { get { return "connectionStringName"; } }
        public static string ApplicationName { get { return "applicationName"; } }
        public static string EnablePasswordReset { get { return "enablePasswordReset"; } }
        public static string EnablePasswordRetrieval { get { return "enablePasswordRetrieval"; } }
        public static string RequiresQuestionAndAnswer { get { return "requiresQuestionAndAnswer"; } }
        public static string RequiresUniqueEmail { get { return "requiresUniqueEmail"; } }
        public static string MaxInvalidPasswordAttempts { get { return "maxInvalidPasswordAttempts"; } }
        public static string PasswordAttemptWindow { get { return "passwordAttemptWindow"; } }
        public static string PasswordFormat { get { return ""; } }
        public static string MinRequiredNonAlphanumericCharacters { get { return "minRequiredAlphaNumericCharacters"; } }
        public static string MinRequiredPasswordLength { get { return "minRequiredPasswordLength"; } }
        public static string PasswordStrengthRegularExpression { get { return "passwordStrengthRegularExpression"; } }
    }

    public class TSPMembershipProvider //:MembershipProvider
    {

        #region Class Variables

        private int _NewPasswordLength = 8;
        private string _ConnectionString;
        private string _ApplicationName;
        private bool _EnablePasswordReset;
        private bool _EnablePasswordRetrieval;
        private bool _RequiresQuestionAndAnswer;
        private bool _RequiresUniqueEmail;
        private int _MaxInvalidPasswordAttempts;
        private int _PasswordAttemptWindow;
        private MembershipPasswordFormat _PasswordFormat;
        private int _MinRequiredNonAlphanumericCharacters;
        private int _MinRequiredPasswordLength;
        private string _PasswordStrengthRegularExpression;
        private MachineKeySection _MachineKey;

        #endregion

        #region Enums

        private enum FailureType
        {
            Password = 1,
            PasswordAnswer = 2
        }

        #endregion

        #region Properties

        public string ApplicationName
        {
            get
            {
                return _ApplicationName;
            }
            set
            {
                _ApplicationName = value;
            }
        }

        public bool EnablePasswordReset { get { return _EnablePasswordReset; } }

        public bool EnablePasswordRetrieval { get { return _EnablePasswordRetrieval; } }

        public bool RequiresQuestionAndAnswer { get { return _RequiresQuestionAndAnswer; } }

        public bool RequiresUniqueEmail { get { return _RequiresUniqueEmail; } }

        public int MaxInvalidPasswordAttempts { get { return _MaxInvalidPasswordAttempts; } }

        public int PasswordAttemptWindow { get { return _PasswordAttemptWindow; } }

        public MembershipPasswordFormat PasswordFormat { get { return _PasswordFormat; } }
        
        public int MinRequiredNonAlphanumericCharacters { get { return _MinRequiredNonAlphanumericCharacters; } }

        public int MinRequiredPasswordLength { get { return _MinRequiredPasswordLength; } }

        public string PasswordStrengthRegularExpression { get { return _PasswordStrengthRegularExpression; } }

        #endregion

        #region Initialization

        /// <summary>
        /// A custom Membership provider for my The share page website that also warps Facebook related user management tasks
        /// </summary>
        /// <param name="name">The name of the Application, by default its The Sharepage</param>
        /// <param name="config">A name value collection of configuration settings</param>
        public void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (name == null || name.Length == 0)
            {
                name = "TSPMembershipProvider";
            }

            _ApplicationName = GetConfigValue(config[TSPmembershipSettingStrings.ConnectionString], System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            _MaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config[TSPmembershipSettingStrings.MaxInvalidPasswordAttempts], "5"));
            _PasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config[TSPmembershipSettingStrings.PasswordAttemptWindow], "10"));
            _MinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config[TSPmembershipSettingStrings.MinRequiredNonAlphanumericCharacters], "0"));
            _MinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config[TSPmembershipSettingStrings.MinRequiredPasswordLength], "7"));
            _PasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config[TSPmembershipSettingStrings.PasswordStrengthRegularExpression], String.Empty));
            _EnablePasswordReset = Convert.ToBoolean(GetConfigValue(config[TSPmembershipSettingStrings.EnablePasswordReset], "true"));
            _EnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config[TSPmembershipSettingStrings.EnablePasswordRetrieval], "false"));
            _RequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config[TSPmembershipSettingStrings.RequiresQuestionAndAnswer], "false"));
            _RequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config[TSPmembershipSettingStrings.RequiresUniqueEmail], "true"));

            string temp_format = config["passwordFormat"];
            if (temp_format == null)
            {
                temp_format = "Hashed";
            }

            switch (temp_format)
            {
                case "Hashed":
                    _PasswordFormat = MembershipPasswordFormat.Hashed;
                    break;
                case "Encrypted":
                    _PasswordFormat = MembershipPasswordFormat.Encrypted;
                    break;
                case "Clear":
                    _PasswordFormat = MembershipPasswordFormat.Clear;
                    break;
                default:
                    throw new ProviderException("Password format not supported.");
            }
            
            ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config[TSPmembershipSettingStrings.ConnectionString]];

            if ((ConnectionStringSettings == null) || (ConnectionStringSettings.ConnectionString.Trim() == String.Empty))
            {
                throw new ProviderException("Connection string cannot be blank.");
            }

            _ConnectionString = ConnectionStringSettings.ConnectionString;

            //Get encryption and decryption key information from the configuration.
            Configuration cfg = WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            _MachineKey = cfg.GetSection("system.web/machineKey") as MachineKeySection;

            if (_MachineKey.ValidationKey.Contains("AutoGenerate"))
            {
                if (PasswordFormat != MembershipPasswordFormat.Clear)
                {
                    throw new ProviderException("Hashed or Encrypted passwords are not supported with auto-generated keys.");
                }
            }
        }

        private string GetConfigValue(string configValue, string defaultValue)
        {
            if (configValue.IsEmptyOrNull())
            {
                return defaultValue;
            }

            return configValue;
        }

        #endregion

        #region Implemented Abstract Methods from MembershipProvider

        /// <summary>
        /// Change the user password.
        /// </summary>
        /// <param name="useremail">UserEmailID</param>
        /// <param name="oldPwd">Old password.</param>
        /// <param name="newPwd">New password.</param>
        /// <returns>T/F if password was changed.</returns>
        public override bool ChangePassword(string useremail, string oldPwd, string newPwd)
        {

            if (!ValidateUser(useremail, oldPwd))
            {
                return false;
            }

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(useremail, newPwd, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                else
                {
                    throw new Exception("Change password canceled due to new password validation failure.");
                }
            }

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_ChangePassword", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@Password", SqlDbType.NVarChar, 255).Value = EncodePassword(newPwd);
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                //Add exception handling here.
                return false;
            }
            finally
            {
                if ((sqlConnection != null) && (sqlConnection.State == ConnectionState.Open)) { sqlConnection.Close(); }
            }

            return true;

        }

        /// <summary>
        /// Change the question and answer for a password validation.
        /// </summary>
        /// <param name="useremail">UserEmail ID.</param>
        /// <param name="password">Password.</param>
        /// <param name="newPwdQuestion">New question text.</param>
        /// <param name="newPwdAnswer">New answer text.</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public override bool ChangePasswordQuestionAndAnswer(
        string useremail,
          string password,
         string newPwdQuestion,
          string newPwdAnswer)
        {

            if (!ValidateUser(useremail, password))
            {
                return false;
            }

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_ChangePasswordQuestionAnswer", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@Question", SqlDbType.NVarChar, 255).Value = newPwdQuestion;
            sqlCommand.Parameters.Add("@Answer", SqlDbType.NVarChar, 255).Value = EncodePassword(newPwdAnswer);
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail ;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                //Add exception handling here.
                return false;
            }
            finally
            {
                if ((sqlConnection != null) && (sqlConnection.State == ConnectionState.Open)) { sqlConnection.Close(); }
            }

            return true;

        }

        /// <summary>
        /// Create a new user
        /// </summary>
        /// <param name="username">Name of the user</param>
        /// <param name="password">Password</param>
        /// <param name="email">User's Email ID</param>
        /// <param name="passwordQuestion">Password Question</param>
        /// <param name="passwordAnswer">Password Answer</param>
        /// <param name="isApproved">IsApproved</param>
        /// <param name="myCustomFacebookData">Facebook User Data</param>
        /// <param name="status">Return value, Creation status as MembershipCreateStatus</param>
        /// <returns></returns>
        public override MembershipUser CreateUser(
            string username,
            string password,
            string email,
            string passwordQuestion,
            string passwordAnswer,
            bool isApproved,
            object myCustomFacebookData,
            out MembershipCreateStatus status)
        {

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(email, password, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            if ((RequiresUniqueEmail && (GetUserNameByEmail(email) != String.Empty)))
            {
                status = MembershipCreateStatus.DuplicateEmail;
                return null;
            }

            MembershipUser membershipUser = GetUser(email, false);

            if (membershipUser == null)
            {
                System.DateTime createDate = DateTime.Now;
                //HACK: Just a work around to pass custom data. Find a clean solution
                FacebookMemberUserData FacebookCustomData = (FacebookMemberUserData)myCustomFacebookData;
                
                SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
                SqlCommand sqlCommand = new SqlCommand("User_Ins", sqlConnection);

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
                sqlCommand.Parameters.Add("@FacebookUid", SqlDbType.BigInt).Value = FacebookCustomData.FBUid;
                sqlCommand.Parameters.Add("@Email", SqlDbType.NVarChar,255).Value = FacebookCustomData.EmailID;
                sqlCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 255).Value = FacebookCustomData.UserName;
                sqlCommand.Parameters.Add("@OAuthAccessToken", SqlDbType.NVarChar, 255).Value = FacebookCustomData.OAuthAccessToken;
                if (FacebookCustomData.ExpiresOn != null && FacebookCustomData.ExpiresOn != DateTime.MinValue)
                {
                    sqlCommand.Parameters.Add("@ExpiresOn", SqlDbType.DateTime).Value = FacebookCustomData.ExpiresOn;
                }
                else { sqlCommand.Parameters.Add("@ExpiresOn", SqlDbType.DateTime).Value = DBNull.Value; }
                if (!FacebookCustomData.ExtendedPermissions.IsEmptyOrNull())
                {
                    sqlCommand.Parameters.Add("@ExtendedPermissions", SqlDbType.NVarChar, 384).Value = FacebookCustomData.ExtendedPermissions;
                }
                else { sqlCommand.Parameters.Add("@ExtendedPermissions", SqlDbType.NVarChar, 384).Value = DBNull.Value; }
                sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;
                sqlCommand.Parameters.Add("@Password", SqlDbType.NVarChar, 255).Value = EncodePassword(password);
                sqlCommand.Parameters.Add("@PasswordQuestion", SqlDbType.NVarChar, 255).Value = passwordQuestion;
                sqlCommand.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar, 255).Value = EncodePassword(passwordAnswer);
                sqlCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = true;//TODO: Check isapproved = true dosent mess with membership provider later
                sqlCommand.Parameters.Add("@Comment", SqlDbType.NVarChar, 255).Value = String.Empty;

                try
                {
                    sqlConnection.Open();

                    sqlCommand.ExecuteNonQuery();
                    if ((int)sqlCommand.Parameters["@returnValue"].Value == 0)
                    {

                        status = MembershipCreateStatus.Success;
                    }
                    else
                    {
                        status = MembershipCreateStatus.UserRejected;
                    }
                }
                catch (SqlException e)
                {
                    //Add exception handling here.

                    status = MembershipCreateStatus.ProviderError;
                }
                finally
                {
                    sqlConnection.Close();
                }
                //HACK: Passes email id insted of username
                return GetUser(FacebookCustomData.EmailID, false);
            }
            else
            {
                status = MembershipCreateStatus.DuplicateUserName;
            }

            return null;
        }
        /// <summary>
        /// Delete a user.
        /// </summary>
        /// <param name="email">UserEmailID.</param>
        /// <param name="deleteAllRelatedData">Whether to delete all related data.</param>
        /// <returns>T/F if the user was deleted.</returns>
        public override bool DeleteUser(
         string email,
         bool deleteAllRelatedData
        )
        {

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Del", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = email; ;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                if ((int)sqlCommand.Parameters["@returnValue"].Value == 0)
                {
                    //HACK: I am already cascading delete, no option for retaining data
                    if (deleteAllRelatedData)
                    {
                        //Process commands to delete all data for the user in the database.
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                sqlConnection.Close();
            }

            return true;

        }
        /// <summary>
        /// Get a collection of users.
        /// </summary>
        /// <param name="pageIndex">Page index.</param>
        /// <param name="pageSize">Page size.</param>
        /// <param name="totalRecords">Total # of records to retrieve.</param>
        /// <returns>Collection of MembershipUser objects.</returns>

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_Sel", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            MembershipUserCollection users = new MembershipUserCollection();

            SqlDataReader sqlDataReader = null;
            totalRecords = 0;

            try
            {
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (sqlDataReader.Read())
                {
                    if (counter >= startIndex)
                    {
                        users.Add(GetUserFromReader(sqlDataReader));
                    }

                    if (counter >= endIndex) { sqlCommand.Cancel(); }
                    counter += 1;
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
            }

            return users;

        }
        /// <summary>
        /// Gets the number of users currently on-line.
        /// </summary>
        /// <returns>  Number of users on-line.</returns>
        public override int GetNumberOfUsersOnline()
        {

            TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_NumberOnline", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;
            sqlCommand.Parameters.Add("@CompareDate", SqlDbType.DateTime).Value = compareTime;

            int numOnline = 0;

            try
            {
                sqlConnection.Open();

                numOnline = Convert.ToInt32(sqlCommand.ExecuteScalar());
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                sqlConnection.Close();
            }

            return numOnline;

        }
        /// <summary>
        /// Get the password for a user.
        /// </summary>
        /// <param name="useremail">User EmailID.</param>
        /// <param name="answer">Answer to security question.</param>
        /// <returns>Password for the user.</returns>
        public override string GetPassword(
         string useremail,
         string answer
        )
        {

            if (!EnablePasswordRetrieval)
            {
                throw new ProviderException("Password Retrieval Not Enabled.");
            }

            if (PasswordFormat == MembershipPasswordFormat.Hashed)
            {
                throw new ProviderException("Cannot retrieve Hashed passwords.");
            }

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_GetPassword", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            string password = String.Empty;
            string passwordAnswer = String.Empty;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow & CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();

                    if (sqlDataReader.GetBoolean(2))
                    {
                        throw new MembershipPasswordException("The supplied user is locked out.");
                    }

                    password = sqlDataReader.GetString(0);
                    passwordAnswer = sqlDataReader.GetString(1);
                }
                else
                {
                    throw new MembershipPasswordException("The supplied user email is not found.");
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
            {
                UpdateFailureCount(useremail, FailureType.PasswordAnswer);

                throw new MembershipPasswordException("Incorrect password answer.");
            }

            if (PasswordFormat == MembershipPasswordFormat.Encrypted)
            {
                password = UnEncodePassword(password);
            }

            return password;
        }


        /// <summary>
        /// Get User as a MembershipUser for a given EmailID
        /// </summary>
        /// <param name="email">User's EmailID</param>
        /// <param name="userIsOnline">True if User is online otherwise false</param>
        /// <returns>A Membership User with Facebook data in provider key (That's my hack)</returns>
        public override MembershipUser GetUser(
        string email,
         bool userIsOnline
        )
        {

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Sel", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = email;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            MembershipUser membershipUser = null;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    membershipUser = GetUserFromReader(sqlDataReader);
                    sqlDataReader.Close();
                    if (userIsOnline)
                    {
                        SqlCommand sqlUpdateCommand = new SqlCommand("User_UpdateActivityDate_ByUserEmail", sqlConnection);
                        sqlConnection.Open();
                        sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                        sqlUpdateCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = email;
                        sqlUpdateCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;
                        sqlUpdateCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            return membershipUser;
        }
        /// <summary>
        /// Get a user based upon Custom facebook data and if they are on-line.
        /// </summary>
        /// <param name="facebookdata">CustomFacebook Data</param>
        /// <param name="userIsOnline">T/F whether the user is on-line.</param>
        /// <returns></returns>
        public override MembershipUser GetUser(
         object facebookdata,
         bool userIsOnline
        )
        {

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_SelByFacebookUid", sqlConnection);
            //HACK: Using Facebook Uid To get user insted of provider key
            FacebookMemberUserData CustomFacebookdata = (FacebookMemberUserData)facebookdata;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@FacebookUid", SqlDbType.BigInt).Value = CustomFacebookdata.FBUid;

            MembershipUser membershipUser = null;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    membershipUser = GetUserFromReader(sqlDataReader);

                    if (userIsOnline)
                    {
                        SqlCommand sqlUpdateCommand = new SqlCommand("User_UpdateActivityDate_ByFacebookUid", sqlConnection);

                        sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                        sqlUpdateCommand.Parameters.Add("@FacebookUid", SqlDbType.BigInt).Value = CustomFacebookdata.FBUid;
                        sqlUpdateCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;
                        sqlUpdateCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            return membershipUser;

        }

        /// <summary>
        /// Unlock a user.
        /// </summary>
        /// <param name="useremail">User Email ID.</param>
        /// <returns>T/F if unlocked.</returns>
        public override bool UnlockUser(
         string useremail
        )
        {

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Unlock", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            try
            {
                sqlConnection.Open();

                sqlCommand.ExecuteNonQuery();
                if ((int)sqlCommand.Parameters["@returnValue"].Value == 0)
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
                return false;
            }
            finally
            {
                sqlConnection.Close();
            }

            return true;

        }

        /// <summary>
        /// Gets a User name by email
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public override string GetUserNameByEmail(string email)
        {
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("UserName_Sel_ByEmail", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 128).Value = email;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            string username = String.Empty;

            try
            {
                sqlConnection.Open();
                username = Convert.ToString(sqlCommand.ExecuteScalar());
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                sqlConnection.Close();
            }

            if (username == null)
            {
                return String.Empty;
            }
            else
            {
                username.Trim();
            }

            return username;

        }
        /// <summary>
        /// Reset the user password.
        /// </summary>
        /// <param name="useremail">User EmailID.</param>
        /// <param name="answer">Answer to security question.</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public override string ResetPassword(
         string useremail,
         string answer
        )
        {

            if (!EnablePasswordReset)
            {
                throw new NotSupportedException("Password Reset is not enabled.");
            }

            if ((answer == null) && (RequiresQuestionAndAnswer))
            {
                UpdateFailureCount(useremail, FailureType.PasswordAnswer);

                throw new ProviderException("Password answer required for password Reset.");
            }

            string newPassword =
              System.Web.Security.Membership.GeneratePassword(
              _NewPasswordLength,
              MinRequiredNonAlphanumericCharacters
              );

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(useremail, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                else
                {
                    throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
                }
            }

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_GetPasswordAnswer", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail;

            int rowsAffected = 0;
            string passwordAnswer = String.Empty;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow & CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();

                    if (sqlDataReader.GetBoolean(1))
                    {
                        throw new MembershipPasswordException("The supplied user is locked out.");
                    }

                    passwordAnswer = sqlDataReader.GetString(0);
                }
                else
                {
                    throw new MembershipPasswordException("The supplied user name is not found.");
                }

                if (RequiresQuestionAndAnswer && (!CheckPassword(answer, passwordAnswer)))
                {
                    UpdateFailureCount(useremail, FailureType.PasswordAnswer);

                    throw new MembershipPasswordException("Incorrect password answer.");
                }

                SqlCommand sqlUpdateCommand = new SqlCommand("User_ChangePassword", sqlConnection);

                sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                sqlUpdateCommand.Parameters.Add("@Password", SqlDbType.NVarChar, 255).Value = EncodePassword(newPassword);
                sqlUpdateCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail;
                sqlUpdateCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;
                rowsAffected = sqlUpdateCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            if (rowsAffected > 0)
            {
                return newPassword;
            }
            else
            {
                throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
            }
        }

        /// <summary>
        /// Update the user information.
        /// </summary>
        /// <param name="_membershipUser">MembershipUser object containing data.</param>
        public override void UpdateUser(MembershipUser membershipUser)
        {
            //TODO: Add code for Updation of user information
            
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Upd", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 128).Value = membershipUser.Email;
            sqlCommand.Parameters.Add("@comment", SqlDbType.NVarChar, 255).Value = membershipUser.Comment;
            sqlCommand.Parameters.Add("@isApproved", SqlDbType.Bit).Value = true; //TODO: Remove this hardcoded true for is approved when possible
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = membershipUser.UserName;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                sqlConnection.Close();
            }

            return;
        }

        /// <summary>
        /// Validate the user based upon username and password.
        /// </summary>
        /// <param name="useremail">UserEmailID.</param>
        /// <param name="password">Password.</param>
        /// <returns>T/F if the user is valid.</returns>
        public override bool ValidateUser(
         string useremail,
         string password
        )
        {

            bool isValid = false;

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Validate", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            SqlDataReader sqlDataReader = null;
            bool isApproved = false;
            string storedPassword = String.Empty;

            try
            {
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    storedPassword = sqlDataReader.GetString(0);
                    isApproved = sqlDataReader.GetBoolean(1);
                }
                else
                {
                    return false;
                }

                sqlDataReader.Close();

                if (CheckPassword(password, storedPassword))
                {
                    if (isApproved)
                    {
                        isValid = true;

                        SqlCommand sqlUpdateCommand = new SqlCommand("User_UpdateLoginDate", sqlConnection);

                        sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                        sqlUpdateCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail;
                        sqlUpdateCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;
                        sqlUpdateCommand.ExecuteNonQuery();
                    }
                }
                else
                {
                    sqlConnection.Close();
                    UpdateFailureCount(useremail, FailureType.Password);
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
                if ((sqlConnection != null) && (sqlConnection.State == ConnectionState.Open)) { sqlConnection.Close(); }
            }

            return isValid;
        }
        /// <summary>
        /// Find all users matching a search string.
        /// </summary>
        /// <param name="usernameToMatch">Search string of user name to match.</param>
        /// <param name="pageIndex">Page number (For next set of records)</param>
        /// <param name="pageSize">Number of records to return in a call</param>
        /// <param name="totalRecords">Total records found.</param>
        /// <returns>Collection of MembershipUser objects.</returns>

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_Sel_ByUserName", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 255).Value = usernameToMatch;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            MembershipUserCollection membershipUsers = new MembershipUserCollection();
            SqlDataReader sqlDataReader = null;
            int counter = 0;

            try
            {
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (sqlDataReader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser membershipUser = GetUserFromReader(sqlDataReader);
                        membershipUsers.Add(membershipUser);
                    }

                    if (counter >= endIndex) { sqlCommand.Cancel(); }

                    counter += 1;
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            totalRecords = counter;

            return membershipUsers;
        }

        /// <summary>
        /// Find all users matching a search string of their email.
        /// </summary>
        /// <param name="emailToMatch">Search string of email to match.</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecords">Total records found.</param>
        /// <returns>Collection of MembershipUser objects.</returns>

        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_Sel_ByEmail", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = emailToMatch;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            MembershipUserCollection membershipUsers = new MembershipUserCollection();
            SqlDataReader sqlDataReader = null;
            int counter = 0;

            try
            {
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (sqlDataReader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser membershipUser = GetUserFromReader(sqlDataReader);
                        membershipUsers.Add(membershipUser);
                    }

                    if (counter >= endIndex) { sqlCommand.Cancel(); }

                    counter += 1;
                }
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            totalRecords = counter;

            return membershipUsers;
        }

        #endregion

        #region "Utility Functions"
        /// <summary>
        /// Create a MembershipUser object from a data reader.
        /// </summary>
        /// <param name="sqlDataReader">Data reader.</param>
        /// <returns>MembershipUser object.</returns>
        private MembershipUser GetUserFromReader(SqlDataReader sqlDataReader)
        {
            //HACK: I am creating this workaround , but will find a good way later.
            
            FacebookMemberUserData FacebookData = new FacebookMemberUserData();
            string username = sqlDataReader.GetString(1);
            string email = sqlDataReader.GetString(2);
            
            string passwordQuestion = String.Empty;
            if (sqlDataReader.GetValue(3) != DBNull.Value)
            {
                passwordQuestion = sqlDataReader.GetString(3);
            }

            string comment = String.Empty;
            if (sqlDataReader.GetValue(4) != DBNull.Value)
            {
                comment = sqlDataReader.GetString(4);
            }

            bool isApproved = sqlDataReader.GetBoolean(5);
            bool isLockedOut = sqlDataReader.GetBoolean(6);
            DateTime creationDate = sqlDataReader.GetDateTime(7);

            DateTime lastLoginDate = new DateTime();
            if (sqlDataReader.GetValue(8) != DBNull.Value)
            {
                lastLoginDate = sqlDataReader.GetDateTime(8);
            }

            DateTime lastActivityDate = sqlDataReader.GetDateTime(9);
            DateTime lastPasswordChangedDate = new DateTime();
            if (sqlDataReader.GetValue(10) != DBNull.Value)
            {
               lastPasswordChangedDate = sqlDataReader.GetDateTime(10);
            }            

            DateTime lastLockedOutDate = new DateTime();
            if (sqlDataReader.GetValue(11) != DBNull.Value)
            {
                lastLockedOutDate = sqlDataReader.GetDateTime(11);
            }
            
            FacebookData.UserName = sqlDataReader.GetString(12);
            FacebookData.FBUid = sqlDataReader.GetInt64(13);
            FacebookData.EmailID = sqlDataReader.GetString(14);
            FacebookData.OAuthAccessToken = sqlDataReader.GetString(15);

            if (sqlDataReader.GetValue(16) != DBNull.Value)
            {
                FacebookData.ExpiresOn = sqlDataReader.GetDateTime(16);
            }

            if (sqlDataReader.GetValue(17) != DBNull.Value)
            {
                FacebookData.ExtendedPermissions = sqlDataReader.GetString(17);
            }

            if (sqlDataReader.GetValue(18) != DBNull.Value)
            {
                FacebookData.LastUpdated = sqlDataReader.GetDateTime(18);
            }

            MembershipUser membershipUser = new MembershipUser(
             this.Name,
             username,
             FacebookData, //HACK: Just a work around to pass facebook data, provider key should have been here
             email,
             passwordQuestion,
             comment,
             isApproved,
             isLockedOut,
             creationDate,
             lastLoginDate,
             lastActivityDate,
             lastPasswordChangedDate,
             lastLockedOutDate
              );

            return membershipUser;

        }

        /// <summary>
        /// Converts a hexadecimal string to a byte array. Used to convert encryption key values from the configuration
        /// </summary>
        /// <param name="hexString">The Hex string to convert</param>
        /// <returns>An array of bytes containing the converted string</returns>
        private byte[] HexToByte(string hexString)
        {
            byte[] returnBytes = new byte[hexString.Length / 2];
            for (int i = 0; i < returnBytes.Length; i++)
                returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            return returnBytes;
        }

        /// <summary>
        /// Update password and answer failure information.
        /// </summary>
        /// <param name="useremail">User Email.</param>
        /// <param name="failureType">Type of failure</param>
        /// <remarks></remarks>
        private void UpdateFailureCount(string useremail, FailureType failureType)
        {

            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_FailedPassword_Upd", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@FailureType", SqlDbType.Int, 0).Value = failureType;
            sqlCommand.Parameters.Add("@PasswordAttempWindow", SqlDbType.DateTime, 0).Value = _PasswordAttemptWindow;
            sqlCommand.Parameters.Add("@MaxInvalidPasswordAttempts", SqlDbType.Int, 0).Value = _MaxInvalidPasswordAttempts;
            sqlCommand.Parameters.Add("@EmailID", SqlDbType.NVarChar, 255).Value = useremail;
            sqlCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 255).Value = _ApplicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception)
            {
                //Add exception handling here.
            }
            finally
            {
                if ((sqlConnection != null) && (sqlConnection.State == ConnectionState.Open)) { sqlConnection.Close(); }
            }

        }
        /// <summary>
        /// Check the password format based upon the MembershipPasswordFormat.
        /// </summary>
        /// <param name="password">Password</param>
        /// <param name="dbpassword">Password From Database</param>
        /// <returns></returns>
        /// <remarks></remarks>
        private bool CheckPassword(string password, string dbpassword)
        {
            string pass1 = password;
            string pass2 = dbpassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Encrypted:
                    pass2 = UnEncodePassword(dbpassword);
                    break;
                case MembershipPasswordFormat.Hashed:
                    pass1 = EncodePassword(password);
                    break;
                default:
                    break;
            }

            if (pass1 == pass2)
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// Encode password.
        /// </summary>
        /// <param name="password">Password.</param>
        /// <returns>Encoded password.</returns>
        private string EncodePassword(string password)
        {
            string encodedPassword = password;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    encodedPassword =
                      Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    HMACSHA1 hash = new HMACSHA1();
                    hash.Key = HexToByte(_MachineKey.ValidationKey.Split(',')[0]);
                    encodedPassword =
                      Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
                    break;
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return encodedPassword;
        }

        /// <summary>
        /// UnEncode password.
        /// </summary>
        /// <param name="encodedPassword">Password.</param>
        /// <returns>Unencoded password.</returns>
        private string UnEncodePassword(string encodedPassword)
        {
            string password = encodedPassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    password =
                      Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    throw new ProviderException("Cannot unencode a hashed password.");
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return password;
        }

        #endregion

    }
    
}
